﻿CREATE TRIGGER [dbo].[tr_Car_VIN] 
ON [dbo].[t_Car] 
FOR INSERT,UPDATE
AS

IF @@ROWCOUNT = 0 RETURN
IF @@ROWCOUNT > 1 BEGIN ROLLBACK TRAN RAISERROR('Multiple VINs update is not supported',16,1) RETURN END
IF TRIGGER_NESTLEVEL(@@PROCID) > 1 RETURN

IF NOT UPDATE (VIN) OR EXISTS(SELECT * FROM inserted i INNER JOIN deleted d ON i.VIN = d.VIN)	RETURN

-- Check VIN
DECLARE @VIN varchar(17) SELECT TOP 1 @VIN = VIN FROM inserted WHERE dbo.VinCheck(VIN) = 0
IF NOT @VIN IS NULL BEGIN	ROLLBACK TRAN	RAISERROR('Incorrect VIN:%s',16,1,@VIN)	RETURN END

IF EXISTS(SELECT * FROM inserted i INNER JOIN deleted d ON i.VIN = d.VIN)RETURN

UPDATE a SET VIN = UPPER(a.VIN)
FROM t_Car a INNER JOIN inserted i ON a.VIN = i.VIN

SELECT @VIN = d.VIN FROM inserted i CROSS JOIN deleted d 
INNER JOIN t_Tracker_Car tc ON d.VIN = tc.VIN
WHERE dbo.VIN_Compare(i.VIN,d.VIN) = 0

IF NOT @VIN IS NULL AND dbo.VinIsID(@VIN) = 0 BEGIN
	DECLARE @TrackerID varchar(20) SELECT @TrackerID = TrackerID FROM t_Tracker_Cat WHERE VIN = @VIN
	ROLLBACK TRAN
	RAISERROR('Can''t change VIN:%s when tracker:%s is installed.',16,1,@VIN,@TrackerID)
	RETURN
END

IF EXISTS(SELECT * FROM inserted i INNER JOIN t_Car c ON c.VIN LIKE '%_'+i.VIN) BEGIN
	ROLLBACK TRAN	RAISERROR('Short VIN is not allowed.',16,1)	RETURN
END

RETURN --==========================================================================================================


